﻿Imports Microsoft.Office.Interop

Public Class xtf_exportarVehiculos
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub xtf_importarVehiculos_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Visible = False
        Try
            If System.IO.File.Exists(Application.StartupPath & "\SBA_plantillas\SBA_Inventario_Vehículos.xls") Then
                Dim Dialog As New SaveFileDialog
                Dialog.Filter = "Microsoft Excel (*.xlsx)|*.xlsx"
                Dialog.FileName = "SBA_Inventario_Vehículos_" & Format(Now, "yyyy-MM-dd_HH-mm-ss") & ".xlsx"
                If Dialog.ShowDialog = Windows.Forms.DialogResult.OK Then
                    System.IO.File.Copy(Application.StartupPath & "\SBA_Plantillas\SBA_Inventario_Vehículos.xlsx", Dialog.FileName, True)
                    Dim xlApp As Excel.Application
                    Dim xlWorkBook As Excel.Workbook
                    Dim xlWorkSheet As Excel.Worksheet
                    Dim misValue As Object = System.Reflection.Missing.Value

                    xlApp = New Excel.ApplicationClass

                    'xlWorkBook = xlApp.Workbooks.Add(misValue)
                    xlWorkBook = xlApp.Workbooks.Open(Dialog.FileName)
                    xlWorkSheet = xlWorkBook.Sheets("Vehículo")

                    'Dim style As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("NewStyle")
                    'style.Font.Bold = True
                    'style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray)

                    armarExcel(xlWorkSheet)

                    'xlWorkSheet.SaveAs (Dialog.FileName)

                    xlWorkBook.Close(True)
                    xlApp.Quit()

                    releaseObject(xlApp)
                    releaseObject(xlWorkBook)
                    releaseObject(xlWorkSheet)

                    ErrManagerShow("001037", Me)
                End If
            Else
                ErrManagerShow("001029", Me)
            End If

        Catch ex As Exception
            ErrManagerShow("001030", Me, ex.Message)
        End Try


        Me.Close()


        ''//Set the row height for a range of rows
        'xlWorkSheet.Rows("1:1").rowheight = 1





    End Sub

    Private Sub armarExcel(ByRef xlWorkSheet As Excel.Worksheet)
            Dim oMissing As Object = System.Reflection.Missing.Value

            'Dim xlsRange As Excel.Range
            'xlsRange = xlWorkSheet.Range("A1", "A1")

            'Dim xlDropDowns As Excel.DropDowns
            'Dim xlDropDown As Excel.DropDown
            'xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
            'xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

            'Add item into drop down list
            'For i As Integer = 0 To ddl_item.Length - 1
            '    xlDropDown.AddItem(ddl_item(i), i + 1)
            'Next

            Dim fila As Integer = 2
            Dim personal_ As New cls_personal_
            If personal_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D7", "D7")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each personal As cls_personal_ In personal_.Items
                    ordenada.Add(personal.apellidonombre, personal.idpersonal & "| " & personal.apellidonombre)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next

            End If

            fila = 2
            Dim gerencia_ As New cls_gerencia_
            If gerencia_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D9", "D9")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each gerencia As cls_gerencia_ In gerencia_.Items
                    ordenada.Add(gerencia.nombre, gerencia.idgerencia & "| " & gerencia.nombre)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next
            End If

            fila = 2
            Dim sector_ As New cls_sector_
            If sector_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D11", "D11")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each sector As cls_sector_ In sector_.Items
                    ordenada.Add(sector.nombre, sector.idsector & "| " & sector.nombre)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next

            End If

            fila = 2
            Dim centrocostoorden_ As New cls_centrocostoorden_
            If centrocostoorden_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D13", "D13")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each centrocostoorden As cls_centrocostoorden_ In centrocostoorden_.Items
                    ordenada.Add(centrocostoorden.codigo & " " & centrocostoorden.nombrecorto, centrocostoorden.idcentrocostoorden & "| " & centrocostoorden.codigo & " " & centrocostoorden.nombrecorto)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next

            End If

            fila = 2
            Dim vehiculotipo_ As New cls_vehiculotipo_
            If vehiculotipo_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D18", "D18")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each vehiculotipo As cls_vehiculotipo_ In vehiculotipo_.Items
                    ordenada.Add(vehiculotipo.nombre, vehiculotipo.idvehiculotipo & "| " & vehiculotipo.nombre)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next

            End If

            fila = 2
            Dim marca_ As New cls_marca_
            If marca_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D20", "D20")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each marca As cls_marca_ In marca_.Items
                    ordenada.Add(marca.nombre, marca.idmarca & "| " & marca.nombre)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next
            End If

            fila = 2
            Dim modelo_ As New cls_modelo_
            If modelo_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D22", "D22")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each modelo As cls_modelo_ In modelo_.Items
                    ordenada.Add(modelo.nombre, modelo.idmodelo & "| " & modelo.nombre)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next
            End If

            fila = 2
            Dim color_ As New cls_color_
            If color_.CargarTodos() Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D24", "D24")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim ordenada As New SortedList
                For Each color As cls_color_ In color_.Items
                    ordenada.Add(color.nombre, color.idcolor & "| " & color.nombre)
                Next
                For Each elemento As Object In ordenada
                    xlDropDown.AddItem(elemento.value)
                Next
                
            End If

            If True Then
                Dim xlsRange As Excel.Range
                xlsRange = xlWorkSheet.Range("D28", "D28")

                Dim xlDropDowns As Excel.DropDowns
                Dim xlDropDown As Excel.DropDown
                xlDropDowns = DirectCast(xlWorkSheet.DropDowns(oMissing), Excel.DropDowns)
                xlDropDown = xlDropDowns.Add(CDbl(xlsRange.Left), CDbl(xlsRange.Top), CDbl(xlsRange.Width), CDbl(xlsRange.Height), True)

                Dim año As Integer = Now.Date.Year
                While año >= 1995
                    xlDropDown.AddItem(año)
                    año -= 1
                End While
            End If


    End Sub
End Class